﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Ini;
using System.IO;
using System.Diagnostics;
namespace WindowsFormsApplication1
{
    public partial class TransRbiForm : Form
    {

        #region db

        #region SQL Server DB

        SqlConnection DBConn;
        SqlCommand DBCmd;
        SqlDataReader DBDataReader;
        SqlParameter param_m;
        #endregion

        #region SQL Server DBMapping

        SqlConnection DBConnMapping;
        SqlCommand DBCmdMapping;
        SqlDataReader DBDataReaderMapping;
        SqlParameter param_mMapping;
        #endregion

        #region SQL Server DBMapping2

        SqlConnection DBConnMapping2;
        SqlCommand DBCmdMapping2;
        SqlDataReader DBDataReaderMapping2;
        SqlParameter param_mMapping2;
        #endregion

        #region SQL Server DBDJ

        SqlConnection DBConnDJ;
        SqlCommand DBCmdDJ;
        SqlDataReader DBDataReaderDJ;
        SqlParameter param_mDJ;
        #endregion

        #endregion

        IniFile SvsIni;
        string connectionStr;
        string RBConnectionStr;
        DateTime d1, d2, d3, d4;
        TimeSpan ts;

        private void LoadPhoto(string BranchID)
        {
            #region count
            DBConn.Open();
            DBCmd = DBConn.CreateCommand();
            if (checkBoxTop10.Checked)
            {
                DBCmd.CommandText = "select top " + numericUpDownTop.Value.ToString() + " count(*) from Zt_RBPhoto where F_BrhID = @F_BrhID ";
            }
            else
            {
                DBCmd.CommandText = "select  count(*) from Zt_RBPhoto where F_BrhID = @F_BrhID ";
            }
            DBCmd.Parameters.Add("@F_BrhID", BranchID);
            DBDataReader = DBCmd.ExecuteReader();
            if (DBDataReader.Read())
            {
                if (checkBoxTop10.Checked)
                {
                    toolStripProgressBarPhoto.Maximum = int.Parse(numericUpDownTop.Value.ToString());
                }
                else
                {
                    toolStripProgressBarPhoto.Maximum = int.Parse(DBDataReader[0].ToString());
                }
                toolStripProgressBarPhoto.Step = 1;
                toolStripProgressBarPhoto.Value = 0;
            }
            DBConn.Close();
            #endregion


            #region startTrans
            DBConn.Open();
            DBCmd = DBConn.CreateCommand();
            if (checkBoxTop10.Checked)
            {
                DBCmd.CommandText = "select top " + numericUpDownTop.Value.ToString() + " * from Zt_RBPhoto where F_BrhID = @F_BrhID ";
            }
            else
            {
                DBCmd.CommandText = "select  * from Zt_RBPhoto where F_BrhID = @F_BrhID ";
            }

            DBCmd.Parameters.Add("@F_BrhID", BranchID);
            DBDataReader = DBCmd.ExecuteReader();
            #endregion

            while (DBDataReader.Read())
            {
                try
                {
                    #region mapping
                    Application.DoEvents();
                    DBConnMapping.Open();
                    DBCmdMapping = DBConnMapping.CreateCommand();
                    DBCmdMapping.CommandText = "select * from Zt_RbImgMapping  where PK_ScanDate =@PK_ScanDate and  PK_MachineID=@PK_MachineID and PK_Seqno =@PK_Seqno";
                    DBCmdMapping.Parameters.Add("@PK_ScanDate", DBDataReader["PK_ScanDate"].ToString());
                    DBCmdMapping.Parameters.Add("@PK_MachineID", DBDataReader["PK_MachineID"].ToString());
                    DBCmdMapping.Parameters.Add("@PK_Seqno", DBDataReader["PK_Seqno"].ToString());
                    DBDataReaderMapping = DBCmdMapping.ExecuteReader();
                    while (DBDataReaderMapping.Read())
                    {
                        string rbiFile = DBDataReaderMapping["PK_ScanDate"].ToString() + "-" + DBDataReaderMapping["PK_MachineID"].ToString() + "-" + DBDataReaderMapping["PK_Seqno"].ToString() + "-" + DBDataReaderMapping["PK_ImgType"].ToString() + "-" + DBDataReaderMapping["F_Set"].ToString() + "-" + DBDataReaderMapping["F_Num"].ToString() + "-" + DBDataReaderMapping["PK_AutoSeq"].ToString() + ".rbi";


                        DBConnDJ.Open();
                        DBCmdDJ = DBConnDJ.CreateCommand();

                        DBCmdDJ.CommandText = "insert into  e_photo  (CustID,PhotoSN,BranchID,eSN,CaseSN) values(@CustID,@PhotoSN,@BranchID,@eSN,@CaseSN)";
                        DBCmdDJ.Parameters.Add("@CustID", DBDataReader["F_CustID"].ToString());
                        DBCmdDJ.Parameters.Add("@PhotoSN", DBDataReaderMapping["PK_ScanDate"].ToString() + "-" + DBDataReaderMapping["PK_MachineID"].ToString() + "-" + DBDataReaderMapping["PK_Seqno"].ToString() + "-" + DBDataReaderMapping["PK_ImgType"].ToString() + "-" + DBDataReaderMapping["F_Set"].ToString() + "-" + DBDataReaderMapping["F_Num"].ToString() + "-" + DBDataReaderMapping["PK_AutoSeq"].ToString() + ".rbi");
                        DBCmdDJ.Parameters.Add("@BranchID", DBDataReader["F_BrhID"].ToString());
                        DBCmdDJ.Parameters.Add("@eSN", DateTime.Now.ToString("yyMMddhhmmssfff"));
                        DBCmdDJ.Parameters.Add("@CaseSN", DateTime.Now.ToString("yyMMddhhmmssfff"));
                        DBCmdDJ.ExecuteNonQuery();
                        DBConnDJ.Close();

                        listBoxPhoto.Items.Add(rbiFile);
                        listBoxPhoto.SelectedIndex = listBoxPhoto.Items.Count - 1;
                        if (listBoxPhoto.Items.Count > 9999)
                        {
                            savePhotoLog();
                        }
                    }
                    #endregion
                    DBConnMapping.Close();
                }
                catch (Exception e)
                {
                    listBoxError.Items.Add(e);
                }
                toolStripProgressBarPhoto.PerformStep();
                toolStripLabelPhoto.Text = toolStripProgressBarPhoto.Value + " / " + toolStripProgressBarPhoto.Maximum;

                while (TransStop)
                {
                    Application.DoEvents();
                    System.Threading.Thread.Sleep(1000);
                }
            }
            DBConn.Close();


        }

        private void LoadID(string BranchID)
        {
            #region count
            DBConn.Open();
            DBCmd = DBConn.CreateCommand();
            if (checkBoxTop10.Checked)
            {
                DBCmd.CommandText = "select top " + numericUpDownTop.Value.ToString() + " count(*) from Zt_RBPersonnel where F_BrhID = @F_BrhID ";
            }
            else
            {
                DBCmd.CommandText = "select  count(*) from Zt_RBPersonnel where F_BrhID = @F_BrhID ";
            }
            DBCmd.Parameters.Add("@F_BrhID", BranchID);
            DBDataReader = DBCmd.ExecuteReader();
            if (DBDataReader.Read())
            {
                if (checkBoxTop10.Checked)
                {
                    toolStripProgressBarID.Maximum = int.Parse(numericUpDownTop.Value.ToString());
                }
                else
                {
                    toolStripProgressBarID.Maximum = int.Parse(DBDataReader[0].ToString());
                }

                toolStripProgressBarID.Step = 1;
                toolStripProgressBarID.Value = 0;
            }
            DBConn.Close();
            #endregion

            DBConn.Open();
            DBCmd = DBConn.CreateCommand();

            if (checkBoxTop10.Checked)
            {
                DBCmd.CommandText = "select top " + numericUpDownTop.Value.ToString() + " * from Zt_RBPersonnel where F_BrhID = @F_BrhID  ";
            }
            else
            {
                DBCmd.CommandText = "select  * from Zt_RBPersonnel where F_BrhID = @F_BrhID  ";
            }
            DBCmd.Parameters.Add("@F_BrhID", BranchID);
            DBDataReader = DBCmd.ExecuteReader();

            List<string> rbiList = new List<string>();
            rbiList.Clear();
            while (DBDataReader.Read())
            {
                d1 = DateTime.Now;

                #region mapping rbi
                Application.DoEvents();
                DBConnMapping.Open();
                DBCmdMapping = DBConnMapping.CreateCommand();
                DBCmdMapping.CommandText = "select * from Zt_RbImgMapping  where PK_ScanDate =@PK_ScanDate and  PK_MachineID=@PK_MachineID and PK_Seqno =@PK_Seqno";
                DBCmdMapping.Parameters.Add("@PK_ScanDate", DBDataReader["PK_ScanDate"].ToString());
                DBCmdMapping.Parameters.Add("@PK_MachineID", DBDataReader["PK_MachineID"].ToString());
                DBCmdMapping.Parameters.Add("@PK_Seqno", DBDataReader["PK_Seqno"].ToString());
                DBDataReaderMapping = DBCmdMapping.ExecuteReader();
                while (DBDataReaderMapping.Read())
                {
                    string rbiFile = DBDataReaderMapping["PK_ScanDate"].ToString() + "-" + DBDataReaderMapping["PK_MachineID"].ToString() + "-" + DBDataReaderMapping["PK_Seqno"].ToString() + "-" + DBDataReaderMapping["PK_ImgType"].ToString() + "-" + DBDataReaderMapping["F_Set"].ToString() + "-" + DBDataReaderMapping["F_Num"].ToString() + "-" + DBDataReaderMapping["PK_AutoSeq"].ToString() + ".rbi";


                    #region 寫入dj.photo

                    DBConnDJ.Open();
                    DBCmdDJ = DBConnDJ.CreateCommand();
                    DBCmdDJ.CommandText = "insert into  e_photoid  (CustID,BranchID,eSN,CaseSN,DocType,ImgFormat) values(@CustID,@BranchID,@eSN,@CaseSN,@DocType,@ImgFormat)";
                    DBCmdDJ.Parameters.Add("@CustID", DBDataReader["F_CustID"].ToString());
                    DBCmdDJ.Parameters.Add("@BranchID", DBDataReader["F_BrhID"].ToString());
                    DBCmdDJ.Parameters.Add("@eSN", rbiFile);
                    DBCmdDJ.Parameters.Add("@CaseSN", rbiFile);
                    DBCmdDJ.Parameters.Add("@DocType", DBDataReaderMapping["F_SubType"].ToString());
                    DBCmdDJ.Parameters.Add("@ImgFormat", DBDataReaderMapping["F_Side"].ToString());
                    DBCmdDJ.ExecuteNonQuery();
                    DBConnDJ.Close();
                    listBoxID.Items.Add(rbiFile + " : " + DBDataReader["F_CustID"].ToString());
                    listBoxID.SelectedIndex = listBoxID.Items.Count - 1;
                    if (listBoxID.Items.Count > 9999)
                    {
                        saveIDLog();
                    }
                    #endregion

                    // rbiList.Add(rbiFile);
                }
                DBConnMapping.Close();
                #endregion



                toolStripProgressBarID.PerformStep();
                toolStripLabelID.Text = toolStripProgressBarID.Value + " / " + toolStripProgressBarID.Maximum.ToString();
                while (TransStop)
                {
                    Application.DoEvents();
                    System.Threading.Thread.Sleep(1000);
                }

            }
            DBConn.Close();
            toolStripProgressBarID.Value = toolStripProgressBarID.Maximum;

        }


        public TransRbiForm()
        {
            InitializeComponent();
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {

        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {

            for (int i = 0; i < checkedListBoxBranch.Items.Count; i++)
            {

                if (checkedListBoxBranch.GetItemChecked(i))
                {
                    LoadPhoto(checkedListBoxBranch.GetItemText(checkedListBoxBranch.Items[i]));
                    LoadID(checkedListBoxBranch.GetItemText(checkedListBoxBranch.Items[i]));

                    listBoxBranchResult.Items.Add(checkedListBoxBranch.GetItemText(checkedListBoxBranch.Items[i]) + ": 完成");
                    checkedListBoxBranch.SetItemChecked(i, false);
                }

            }

            saveLog();

        }


        private void Form1_Load(object sender, EventArgs e)
        {
            SvsIni = new IniFile(System.Windows.Forms.Application.StartupPath + "\\svs.ini");

            connectionStr = "Data Source=" + SvsIni.IniReadValue("Book", "CenterDBServer") +
            ";Initial Catalog=" + SvsIni.IniReadValue("Book", "CenterDB") +
            ";Persist Security Info=True;User ID=" + SvsIni.IniReadValue("Book", "CenterDBUser") +
            ";Password=" + SvsIni.IniReadValue("Book", "CenterDBPW") + ";";

            RBConnectionStr = "Data Source=" + SvsIni.IniReadValue("Book", "RBDBServer") +
";Initial Catalog=" + SvsIni.IniReadValue("Book", "RBDB") +
";Persist Security Info=True;User ID=" + SvsIni.IniReadValue("Book", "RBDBUser") +
";Password=" + SvsIni.IniReadValue("Book", "RBDBPW") + ";";

            DBConn = new SqlConnection(RBConnectionStr);

            DBConnDJ = new SqlConnection(connectionStr);

            DBConnMapping = new SqlConnection(RBConnectionStr);

            DBConnMapping2 = new SqlConnection(RBConnectionStr);

            if (!Directory.Exists(textBoxRbiPath.Text))
            {
                Directory.CreateDirectory(textBoxRbiPath.Text);
            }
        }

        private void toolStripButtonLoadDB_Click(object sender, EventArgs e)
        {
            checkedListBoxBranch.Items.Clear();
            DBConn.Open();
            DBCmd = DBConn.CreateCommand();
            DBCmd.CommandText = "select F_BrhID from Zt_RBPersonnel group by  F_BrhID";
            DBDataReader = DBCmd.ExecuteReader();
            while (DBDataReader.Read())
            {
                checkedListBoxBranch.Items.Add(DBDataReader["F_BrhID"].ToString());
            }
            DBConn.Close();

            for (int i = 0; i < checkedListBoxBranch.Items.Count; i++)
            {
                checkedListBoxBranch.SetItemChecked(i, true);

            }
        }

        private void toolStripButtonLoadTxt_Click(object sender, EventArgs e)
        {
            checkedListBoxBranch.Items.Clear();
            if (File.Exists("branch.lst"))
            {
                System.IO.StreamReader fn = new System.IO.StreamReader("branch.lst", Encoding.Default);
                checkedListBoxBranch.Items.Clear();
                while (fn.EndOfStream == false)
                {
                    checkedListBoxBranch.Items.Add(fn.ReadLine());
                }
                fn.Close();

                for (int i = 0; i < checkedListBoxBranch.Items.Count; i++)
                {
                    checkedListBoxBranch.SetItemChecked(i, true);
                }


            }
        }

        private void TransRbiForm_FormClosed(object sender, FormClosedEventArgs e)
        {

            //DBConn.Close();

            //DBConnMapping.Close();
            //DBConnMapping2.Close();
            //DBConnDJ.Close();
            //Close();
        }

        bool TransStop = false;
        private void toolStripButton2_Click_1(object sender, EventArgs e)
        {
            TransStop = !TransStop;
        }

        private void savePhotoLog()
        {
            string logName = "TransPhoto_" + DateTime.Now.ToString(" yMMdd-hhmmss") + ".log";
            string logStr = "";
            if (listBoxPhoto.Items.Count > 0)
            {

                foreach (string item in listBoxPhoto.Items)
                {
                    logStr = logStr + "\n" + item;
                }

                using (StreamWriter sw = new StreamWriter(logName, true))
                {
                    sw.WriteLine(logStr);
                }
            }
            listBoxPhoto.Items.Clear();
        }

        private void saveIDLog()
        {
            string logName = "TransID_" + DateTime.Now.ToString(" yMMdd-hhmmss") + ".log";
            string logStr = "";
            if (listBoxID.Items.Count > 0)
            {

                foreach (string item in listBoxID.Items)
                {
                    logStr = logStr + "\n" + item;
                }

                using (StreamWriter sw = new StreamWriter(logName, true))
                {
                    sw.WriteLine(logStr);
                }
            }
            listBoxID.Items.Clear();
        }

        private void saveResultLog()
        {
            string logName = "TransOKBranch_" + DateTime.Now.ToString(" yMMdd-hhmmss") + ".log";
            string logStr = "";
            if (listBoxBranchResult.Items.Count > 0)
            {

                foreach (string item in listBoxBranchResult.Items)
                {
                    logStr = logStr + "\n" + item;
                }

                using (StreamWriter sw = new StreamWriter(logName, true))
                {
                    sw.WriteLine(logStr);
                }
            }
            listBoxBranchResult.Items.Clear();
        }

        private void saveImageLog()
        {
            string logName = "TransImage_" + DateTime.Now.ToString(" yMMdd-hhmmss") + ".log";
            string logStr = "";
            if (listBoxImage.Items.Count > 0)
            {

                foreach (string item in listBoxImage.Items)
                {
                    logStr = logStr + "\n" + item;
                }

                using (StreamWriter sw = new StreamWriter(logName, true))
                {
                    sw.WriteLine(logStr);
                }
            }
            listBoxImage.Items.Clear();
        }
        private void saveLog()
        {

            savePhotoLog();

            saveIDLog();

            saveResultLog();

    
        }
        
        private void TransRbiForm_FormClosing(object sender, FormClosingEventArgs e)
        {

        }

        private void transImage(string Sfile, string Dfile)
        {
            System.Diagnostics.Process p = new System.Diagnostics.Process();
            p.StartInfo.FileName = Path.GetDirectoryName(Application.ExecutablePath) + @"\ImgProcessByKey.exe";//需要启动的程序名　　　
            p.StartInfo.Arguments = Sfile + " " + Dfile;//启动参数　　　

            p.Start();//启动　　　
            if (p.HasExited)
            {
                p.Kill();
            }
        }


        public void DirSearch(string sDir)
        {
            try
            {
                //先找出所有目錄
                foreach (string d in Directory.GetDirectories(sDir))
                {

                    //先針對目前目路的檔案做處理
                    foreach (string f in Directory.GetFiles(d))
                    {
                        listBoxImage.Items.Add(f);
                        //if (File.Exists(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(f) + ".bmp"))
                        //{
                        //    File.Delete(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(f) + ".bmp");
                        //}
                        //if (File.Exists(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(f) + ".jpg"))
                        //{
                        //    File.Delete(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(f) + ".jpg");
                        //}
                        //transImage(f, textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(f) + ".bmp");
                        //System.Threading.Thread.Sleep(100);
                        Application.DoEvents();
                        if (d.IndexOf(@"\F\") > 0)
                        {
                            UpdateIdImage(f);
                        }
                        else if (d.IndexOf(@"\G\") > 0)
                        {
                            UpdatePhotoImage(f);
                        }
                        else
                        {
                            toolStripProgressBarPhoto.PerformStep();
                            toolStripLabelPhoto.Text = toolStripProgressBarPhoto.Value + " / " + toolStripProgressBarPhoto.Maximum.ToString();
                        }


                    }
                    //此目錄處理完再針對每個子目錄做處理
                    DirSearch(d);
                }
            }
            catch (System.Exception excpt)
            {
                Console.WriteLine(excpt.Message);
            }
        }

        private void UpdateIdImage(string ImageFile)
        {
            //string jpgFile = textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(ImageFile) + ".jpg";
            FileStream m_filestream = new FileStream(ImageFile, FileMode.Open, FileAccess.Read);
            Byte[] m_byte = new Byte[m_filestream.Length];
            m_filestream.Read(m_byte, 0, m_byte.Length);

            DBConnDJ.Open();
            DBCmdDJ = DBConnDJ.CreateCommand();
            DBCmdDJ.CommandText = "select * from e_photoid where  CaseSN = @CaseSN";
            DBCmdDJ.Parameters.Add("@CaseSN", Path.GetFileName(ImageFile));
            DBDataReaderDJ = DBCmdDJ.ExecuteReader();
            if (DBDataReaderDJ.Read())
            {
                DBConnDJ.Close();
                DBConnDJ.Open();
                DBCmdDJ = DBConnDJ.CreateCommand();
                DBCmdDJ.CommandText = "update  e_photoid set IDImageF = @IDImageF  where  CaseSN = @CaseSN";
                DBCmdDJ.Parameters.Add("@CaseSN", Path.GetFileName(ImageFile));
                DBCmdDJ.Parameters.AddWithValue("@IDImageF", m_byte);
                DBDataReaderDJ = DBCmdDJ.ExecuteReader();
                DBConnDJ.Close();
                m_filestream.Close();
                //if (File.Exists(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(ImageFile) + ".jpg"))
                //{
                //    File.Delete(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(ImageFile) + ".jpg");
                //}
            }
            else
            {
                DBConnDJ.Close();
            }
            toolStripProgressBarPhoto.PerformStep();
            toolStripLabelPhoto.Text = toolStripProgressBarPhoto.Value + " / " + toolStripProgressBarPhoto.Maximum.ToString();
        }

        private void UpdatePhotoImage(string ImageFile)
        {
            FileStream m_filestream = new FileStream(ImageFile, FileMode.Open, FileAccess.Read);
            Byte[] m_byte = new Byte[m_filestream.Length];
            m_filestream.Read(m_byte, 0, m_byte.Length);
            //param_m = new SqlParameter("@idimage", DbType.Binary);//

            DBConnDJ.Open();
            DBCmdDJ = DBConnDJ.CreateCommand();
            DBCmdDJ.CommandText = "select *  from e_photo where  PhotoSN = @PhotoSN";
            DBCmdDJ.Parameters.Add("@PhotoSN", Path.GetFileName(ImageFile));
            DBDataReaderDJ = DBCmdDJ.ExecuteReader();
            if (DBDataReaderDJ.Read())
            {
                DBConnDJ.Close();
                DBConnDJ.Open();
                DBCmdDJ = DBConnDJ.CreateCommand();
                DBCmdDJ.CommandText = "update e_photo set PhotoImage = @PhotoImage  where  PhotoSN = @PhotoSN";
                DBCmdDJ.Parameters.Add("@PhotoSN", Path.GetFileName(ImageFile));
                DBCmdDJ.Parameters.AddWithValue("@PhotoImage", m_byte);
                DBCmdDJ.ExecuteNonQuery();
                DBConnDJ.Close();
                m_filestream.Close();
                //if (File.Exists(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(ImageFile) + ".jpg"))
                //{
                //    File.Delete(textBoxRbiPath.Text + @"\" + Path.GetFileNameWithoutExtension(ImageFile) + ".jpg");
                //}
            }
            else
            {
                DBConnDJ.Close();
            }
            toolStripProgressBarPhoto.PerformStep();
            toolStripLabelPhoto.Text = toolStripProgressBarPhoto.Value + " / " + toolStripProgressBarPhoto.Maximum.ToString();
        }


        public static int GetFilesCount(System.IO.DirectoryInfo dirInfo)
        {
            int totalFile = 0;
            totalFile += dirInfo.GetFiles().Length;
            foreach (System.IO.DirectoryInfo subdir in dirInfo.GetDirectories())
            {
                totalFile += GetFilesCount(subdir);
            }
            return totalFile;
        }

        private void toolStripButtonTransImage_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            
            DirectoryInfo di = new DirectoryInfo(toolStripTextBoxRbiPath.Text);
            FileInfo[] files = di.GetFiles();
            toolStripProgressBarPhoto.Step = 1;
            toolStripProgressBarPhoto.Value = 0;
            toolStripProgressBarPhoto.Maximum = GetFilesCount(di);
            DirSearch(toolStripTextBoxRbiPath.Text);
            saveImageLog();


        }
    }
}
